


ALTER                 PROC sp_fc_bl_shipped_report(
@CUSTOMER_INPUT VARCHAR(100),
@PRODUCT_INPUT VARCHAR(100),
@MONTH_FROM INT,
@MONTH_TO INT,
@YEAR_INPUT INT,
@BL_DATE_CATEGORY INT,
@defaultPrompt varchar(20) )

AS
BEGIN

	
	DECLARE @RESULTS TABLE( CUSTOMER VARCHAR(100), 
				PRODUCT VARCHAR(100),
				MONTH INT,
				YEAR INT,
				SF_QTY INT,
				SF_UNIT_PRICE FLOAT,
				MF_QTY INT,
				MF_UNIT_PRICE FLOAT,
				BL_QTY INT,
				BL_EXT_PRICE FLOAT,
				SHIP_QTY INT,
				SHIP_EXT_PRICE FLOAT,
				--FC_VS_BL_AND_SHIP FLOAT,   -- Commented by Gandhi on 31st Aug 2004
				--FC_VS_SHIP FLOAT,
				--FC_DIFF_BL_AND_SHIP FLOAT,
				--FC_DIFF_SHIP FLOAT,
				SF_CONFIDENCE_LEVEL FLOAT,
				MISC VARCHAR(15) )   --ADDED BY VARMA
	
	DECLARE @ID INT
	DECLARE @USER VARCHAR(50)
	DECLARE @CUSTOMER VARCHAR(100)
	DECLARE @PRODUCT VARCHAR(100)
	DECLARE @SALES_PERSON VARCHAR(100) -- Added by Gandhi on 10th Sep 2004
	DECLARE @END_USER VARCHAR(100) -- Added by Gandhi on 15th Sep 2004
	DECLARE @MONTH INT
	DECLARE @MONTH_NAME VARCHAR(20)
	DECLARE @YEAR INT
	DECLARE @QUANTITY INT
	DECLARE @UNIT_PRICE FLOAT
	DECLARE @CREATED_DATE VARCHAR(20)
	DECLARE @MODIFIED_DATE VARCHAR(20)
	DECLARE @REQ_DATE VARCHAR(20)
	DECLARE @SCH_DATE VARCHAR(20)
	DECLARE @BL_TYPE VARCHAR(20)
	DECLARE @ROW_COUNT INT
	DECLARE @SHIP_QTY INT
	DECLARE @SHIP_EXT_PRICE FLOAT
	DECLARE @BL_QTY INT
	DECLARE @BL_EXT_PRICE FLOAT
	DECLARE @FC_QTY INT
	DECLARE @FC_EXT_PRICE FLOAT
	DECLARE @FC_VS_SHIP FLOAT
	DECLARE @FC_VS_BL_AND_SHIP FLOAT
	DECLARE @FC_DIFF_BL_AND_SHIP FLOAT
	DECLARE @FC_DIFF_SHIP FLOAT
	DECLARE @MKTG_PART_NUMBER VARCHAR(100)

	-- Added by gandhi on 31st Aug 2004
	DECLARE @SF_CONFIDENCE_LEVEL FLOAT  
	DECLARE @MF_CONFIDENCE_LEVEL FLOAT  
	DECLARE @SF_QUANTITY INT
	DECLARE @SF_UNIT_PRICE FLOAT
	DECLARE @MF_QUANTITY INT
	DECLARE @MF_UNIT_PRICE FLOAT
	-- Ends here - Gandhi
	DECLARE @MISC VARCHAR(15)	--added by varma
	DECLARE @FC_CURSOR CURSOR 
	DECLARE @BL_CURSOR CURSOR
	DECLARE @SHIP_CURSOR CURSOR
	DECLARE @RESULTS_CURSOR CURSOR
	
	
	-- INSERT FORECAST DETAILS
	SET @FC_CURSOR = CURSOR FOR
	SELECT * FROM fn_ct_mkg_load_marketing_forecast_details( @CUSTOMER_INPUT, @PRODUCT_INPUT, @MONTH_FROM, @MONTH_TO, @YEAR_INPUT )
	
	OPEN @FC_CURSOR
		FETCH NEXT FROM @FC_CURSOR
		INTO @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @SF_QUANTITY, @SF_UNIT_PRICE, @SF_CONFIDENCE_LEVEL, @MF_QUANTITY, @MF_UNIT_PRICE, @MF_CONFIDENCE_LEVEL, @MISC
	
		
		WHILE @@FETCH_STATUS = 0
		BEGIN	
			/*  Commented by gandhi on 31st Aug 2004
			SELECT @ROW_COUNT = COUNT(*) FROM @RESULTS 
			WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR

			IF @ROW_COUNT = 0
			BEGIN*/
				INSERT INTO @RESULTS ( CUSTOMER, PRODUCT, MONTH, YEAR, SF_QTY, SF_UNIT_PRICE, MF_QTY, MF_UNIT_PRICE, BL_QTY, BL_EXT_PRICE, SHIP_QTY, SHIP_EXT_PRICE, SF_CONFIDENCE_LEVEL, MISC )
				VALUES ( @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @SF_QUANTITY, @SF_UNIT_PRICE, @MF_QUANTITY, @MF_UNIT_PRICE, 0, 0.0, 0, 0.0, @SF_CONFIDENCE_LEVEL,@MISC )
			
				/*SELECT @MKTG_PART_NUMBER = Product.P_Marketing_part_Number from product,Process_Step  where Product.Part_Number = @PRODUCT and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID  
				
				IF( @MKTG_PART_NUMBER IS NULL)
				BEGIN
					UPDATE @RESULTS SET PRODUCT =  @PRODUCT WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR	
				END
				ELSE
				BEGIN
					UPDATE @RESULTS SET PRODUCT =  @MKTG_PART_NUMBER WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR	
				END*/

			/*END

			ELSE
			BEGIN
				SELECT @FC_QTY = ISNULL(FC_QTY, 0), @FC_EXT_PRICE = ISNULL(FC_EXT_PRICE, 0.0) FROM @RESULTS
				WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR

				UPDATE @RESULTS SET FC_QTY = (@FC_QTY + @QUANTITY), FC_EXT_PRICE = ( @FC_EXT_PRICE + @UNIT_PRICE )
				WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR
			END*/
		
			FETCH NEXT FROM @FC_CURSOR
			INTO @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @SF_QUANTITY, @SF_UNIT_PRICE, @SF_CONFIDENCE_LEVEL, @MF_QUANTITY, @MF_UNIT_PRICE, @MF_CONFIDENCE_LEVEL, @MISC
		END
	CLOSE @FC_CURSOR
	DEALLOCATE @FC_CURSOR
	
	
	-- INSERT BACKLOG DETAILS
	DECLARE @TODAY VARCHAR(20)
	SET @TODAY = CONVERT(VARCHAR, GETDATE(), 101)

	-- Added by gandhi on 7th Aug 2004 to pass @Backlog_Type as parameter to fn_ct_sls_load_backlog() to display both open and hold backlogs 
	DECLARE @BACKLOG_TYPE INT
	SET @BACKLOG_TYPE = 2
	-- Ends here - gandhi	

	SET @BL_CURSOR = CURSOR FOR
	SELECT * FROM fn_ct_sls_load_backlog( @CUSTOMER_INPUT, @PRODUCT_INPUT, @BL_DATE_CATEGORY, @MONTH_FROM, @MONTH_TO, @YEAR_INPUT, @TODAY, @BACKLOG_TYPE )--CONVERT(VARCHAR, GETDATE(), 101) )
	
	OPEN @BL_CURSOR
		FETCH NEXT FROM @BL_CURSOR
		INTO @USER, @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE, @END_USER
	
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
	
			SELECT @ROW_COUNT = COUNT(*) FROM @RESULTS 
			WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR

			IF @ROW_COUNT = 0
			BEGIN
				INSERT INTO @RESULTS ( CUSTOMER, PRODUCT, MONTH, YEAR, SF_QTY, SF_UNIT_PRICE, MF_QTY, MF_UNIT_PRICE, BL_QTY, BL_EXT_PRICE, SHIP_QTY, SHIP_EXT_PRICE, SF_CONFIDENCE_LEVEL, MISC )
				VALUES ( @CUSTOMER, @PRODUCT, @MONTH, @YEAR, 0, 0.0, 0, 0.0, ISNULL( @QUANTITY, 0 ), ( ISNULL( @QUANTITY, 0 ) * @UNIT_PRICE ), 0, 0.0, 0.0, '' )
			END

			ELSE
			BEGIN
				SELECT @BL_QTY = ISNULL(BL_QTY, 0), @BL_EXT_PRICE = ISNULL(BL_EXT_PRICE, 0.0) FROM @RESULTS
				WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR

				UPDATE @RESULTS SET BL_QTY = (@BL_QTY + @QUANTITY), BL_EXT_PRICE = ( @BL_EXT_PRICE + ( @QUANTITY*@UNIT_PRICE ) )
				WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR
			END
	
			FETCH NEXT FROM @BL_CURSOR
			INTO @USER, @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE, @END_USER
		END

	CLOSE @BL_CURSOR
	DEALLOCATE @BL_CURSOR


	-- INSERT SHIPPED DETAILS

	DECLARE @SALES_PERSON_INPUT VARCHAR(100)
	SET @SALES_PERSON_INPUT = '-1'

	SET @SHIP_CURSOR = CURSOR FOR
	SELECT * FROM fn_shipped_orders_by_month_for_fcbl_report( @CUSTOMER_INPUT, @PRODUCT_INPUT, @SALES_PERSON_INPUT, @MONTH_FROM, @MONTH_TO, @YEAR_INPUT )
	
	OPEN @SHIP_CURSOR	
		FETCH NEXT FROM @SHIP_CURSOR
		INTO @CUSTOMER, @PRODUCT, @SALES_PERSON, @END_USER, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
	
			SELECT @ROW_COUNT = COUNT(*) FROM @RESULTS 
			WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR

			IF @ROW_COUNT = 0
			BEGIN
				INSERT INTO @RESULTS ( CUSTOMER, PRODUCT, MONTH, YEAR, SF_QTY, SF_UNIT_PRICE, MF_QTY, MF_UNIT_PRICE, BL_QTY, BL_EXT_PRICE, SHIP_QTY, SHIP_EXT_PRICE, SF_CONFIDENCE_LEVEL, MISC )
				VALUES ( @CUSTOMER, @PRODUCT, @MONTH, @YEAR, 0, 0.0, 0, 0.0, 0, 0.0, @QUANTITY, ( ISNULL( @QUANTITY, 0 )* @UNIT_PRICE ), 0.0, '')
			END

			ELSE
			BEGIN
				SELECT @SHIP_QTY = ISNULL(SHIP_QTY, 0), @SHIP_EXT_PRICE = ISNULL(SHIP_EXT_PRICE, 0.0) FROM @RESULTS
				WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR
				
				UPDATE @RESULTS SET SHIP_QTY = (@SHIP_QTY + @QUANTITY), SHIP_EXT_PRICE = ((@QUANTITY*@UNIT_PRICE) + @SHIP_EXT_PRICE)
				WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR
			END
	
			FETCH NEXT FROM @SHIP_CURSOR
			INTO @CUSTOMER, @PRODUCT, @SALES_PERSON, @END_USER, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE
		END
	CLOSE @SHIP_CURSOR
	DEALLOCATE @SHIP_CURSOR
	
	-- Commented by Gandhi on 31st Aug 2004
	/*SET @RESULTS_CURSOR = CURSOR FOR
	SELECT * FROM @RESULTS
	
	OPEN @RESULTS_CURSOR
		FETCH NEXT FROM @RESULTS_CURSOR
		INTO @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @FC_QTY, @FC_EXT_PRICE, @BL_QTY, @BL_EXT_PRICE, @SHIP_QTY, @SHIP_EXT_PRICE, @FC_VS_BL_AND_SHIP, @FC_VS_SHIP, @FC_DIFF_BL_AND_SHIP, @FC_DIFF_SHIP, @CONFIDENCE_LEVEL
		
		WHILE @@FETCH_STATUS = 0
		BEGIN

			IF @FC_EXT_PRICE IS NOT NULL AND @FC_EXT_PRICE <> 0
			BEGIN
				SET @FC_VS_SHIP = ( ISNULL(@SHIP_EXT_PRICE, 0) * 100 ) / ( ( ISNULL(@FC_EXT_PRICE, 0) / 30 ) * DATEPART( DAY, GETDATE() ) )
				SET @FC_VS_BL_AND_SHIP = ( ( ISNULL(@BL_EXT_PRICE, 0) + ISNULL(@SHIP_EXT_PRICE, 0) ) * 100 ) / ( ( ISNULL(@FC_EXT_PRICE, 0) / 30 ) * DATEPART( DAY, GETDATE() ) )
			END
			ELSE
			BEGIN
				SET @FC_VS_SHIP = 0
				SET @FC_VS_BL_AND_SHIP = 0
			END

			SET @FC_DIFF_BL_AND_SHIP = ISNULL(@FC_EXT_PRICE, 0) - (ISNULL(@BL_EXT_PRICE, 0) + ISNULL(@SHIP_EXT_PRICE, 0) )
			SET @FC_DIFF_SHIP = ISNULL(@FC_EXT_PRICE, 0) - ISNULL(@SHIP_EXT_PRICE, 0)

			UPDATE @RESULTS SET FC_VS_BL_AND_SHIP = @FC_VS_BL_AND_SHIP, FC_VS_SHIP = @FC_VS_SHIP, 
			FC_DIFF_BL_AND_SHIP = @FC_DIFF_BL_AND_SHIP, FC_DIFF_SHIP = @FC_DIFF_SHIP
			WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR			

			FETCH NEXT FROM @RESULTS_CURSOR
			INTO @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @FC_QTY, @FC_EXT_PRICE, @BL_QTY, @BL_EXT_PRICE, @SHIP_QTY, @SHIP_EXT_PRICE, @FC_VS_BL_AND_SHIP, @FC_VS_SHIP, @FC_DIFF_BL_AND_SHIP, @FC_DIFF_SHIP, @CONFIDENCE_LEVEL
		END	
	CLOSE @RESULTS_CURSOR
	DEALLOCATE @RESULTS_CURSOR*/

	SELECT * FROM @RESULTS

END

